﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Runtime.InteropServices;

using Microsoft.Office.Interop.Excel;

namespace Lottery.WPSOffice {
    /// <summary>
    /// 电子表格生成器
    /// </summary>
    public static class BatchGen {
        [DllImport("shell32.dll")]
        private static extern int ShellExecute(IntPtr hwnd, StringBuilder lpszOp, StringBuilder lpszFile, StringBuilder lpszParams, StringBuilder lpszDir, int FsShowCmd);

        public static List<List<string>> Import310Data(string fileName) {
            var result = new List<List<string>>();
            // 创建Excel
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            var row = 5;
            var rowOffset = 0;
            while (true) {
                var cell = ws.Cells[row, 1] as Range;
                if (string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    rowOffset++;
                }

                row++;
            }

            var COL_COUNT = 1 + 14 * 3;
            if (rowOffset > 1) {
                var r = ws.get_Range(ws.Cells[5, 1], ws.Cells[4 + rowOffset, COL_COUNT]);
                var d = r.Value2 as object[,];

                var rowCount = d.Length / COL_COUNT;
                for (int i = 1; i <= rowCount; i++) {
                    var rowLine = new List<string>();
                    for (int j = 1; j <= COL_COUNT; j++) {
                        var c = d[i, j] == null ? string.Empty : d[i, j].ToString();
                        rowLine.Add(c);
                    }
                    result.Add(rowLine);
                }
            } else if (rowOffset == 1) {
                /* 
                 * 当rowOffset为1的时候
                 * var r = ws.get_Range(ws.Cells[3, 1], ws.Cells[2 + rowOffset, COL_COUNT]);
                 * var d = r.Value2 as object[,];
                 * 这段代码中d会等于null
                 * 所以才采用下面特别的写法。
                 */
                var rowLine = new List<string>();
                for (int i = 1; i <= COL_COUNT; i++) {
                    var c = (ws.Cells[5, i] as Range).Text;
                    rowLine.Add(c.ToString());
                }
                result.Add(rowLine);
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }

        /// <summary>
        /// 导入预测数据
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static List<List<string>> ImportYuCeData(string fileName) {
            var result = new List<List<string>>();
            // 创建Excel
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            var row = 5;
            var rowOffset = 0;
            while (true) {
                var cell = ws.Cells[row, 1] as Range;
                if (string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    rowOffset++;
                }

                row++;
            }

            var COL_COUNT = 1 + 14;
            if (rowOffset > 1) {
                var r = ws.get_Range(ws.Cells[5, 1], ws.Cells[4 + rowOffset, COL_COUNT]);
                var d = r.Value2 as object[,];

                var rowCount = d.Length / COL_COUNT;
                for (int i = 1; i <= rowCount; i++) {
                    var rowLine = new List<string>();
                    for (int j = 1; j <= COL_COUNT; j++) {
                        var c = d[i, j] == null ? string.Empty : d[i, j].ToString();
                        rowLine.Add(c);
                    }
                    result.Add(rowLine);
                }
            } else if (rowOffset == 1) {
                /* 
                 * 当rowOffset为1的时候
                 * var r = ws.get_Range(ws.Cells[3, 1], ws.Cells[2 + rowOffset, COL_COUNT]);
                 * var d = r.Value2 as object[,];
                 * 这段代码中d会等于null
                 * 所以才采用下面特别的写法。
                 */
                var rowLine = new List<string>();
                for (int i = 1; i <= COL_COUNT; i++) {
                    var c = (ws.Cells[5, i] as Range).Text;
                    rowLine.Add(c.ToString());
                }
                result.Add(rowLine);
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }

        /// <summary>
        /// 导入赔率数据
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static List<List<string>> ImportPeiLvData(string fileName) {
            var result = new List<List<string>>();
            // 创建Excel
            var app = new ApplicationClass();
            app.Visible = false;

            app.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Worksheet ws = app.ActiveWorkbook.ActiveSheet as Worksheet;

            var row = 5;
            var rowOffset = 0;
            while (true) {
                var cell = ws.Cells[row, 1] as Range;
                if (string.IsNullOrEmpty(cell.Text.ToString())) {
                    break;
                } else {
                    rowOffset++;
                }

                row++;
            }

            var COL_COUNT = 1 + 14;
            if (rowOffset > 1) {
                var r = ws.get_Range(ws.Cells[5, 1], ws.Cells[4 + rowOffset, COL_COUNT]);
                var d = r.Value2 as object[,];

                var rowCount = d.Length / COL_COUNT;
                for (int i = 1; i <= rowCount; i++) {
                    var rowLine = new List<string>();
                    for (int j = 1; j <= COL_COUNT; j++) {
                        var c = d[i, j] == null ? string.Empty : d[i, j].ToString();
                        rowLine.Add(c);
                    }
                    result.Add(rowLine);
                }
            } else if (rowOffset == 1) {
                /* 
                 * 当rowOffset为1的时候
                 * var r = ws.get_Range(ws.Cells[3, 1], ws.Cells[2 + rowOffset, COL_COUNT]);
                 * var d = r.Value2 as object[,];
                 * 这段代码中d会等于null
                 * 所以才采用下面特别的写法。
                 */
                var rowLine = new List<string>();
                for (int i = 1; i <= COL_COUNT; i++) {
                    var c = (ws.Cells[5, i] as Range).Text;
                    rowLine.Add(c.ToString());
                }
                result.Add(rowLine);
            }

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return result;
        }


        /// <summary>
        /// 为表一生成Excel格式的统计表
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="data">统计数据</param>
        /// <param name="xhList">序号</param>
        /// <param name="md">媒体数据</param>
        public static void ExportT1(string fileName, List<List<string>> data, List<string> xhList,
            List<string> md) {
            var d = new List<string>();
            foreach (var item in data) {
                foreach (var it in item) {
                    d.Add(it);
                }
            }

            // 创建ET
            var app = new ApplicationClass();
            app.Visible = false;
            app.UserName = " ";

            //增加工作表
            app.Workbooks.Add(Type.Missing);
            //删除多余的两个Sheet
            app.ActiveWindow.SelectedSheets.Delete();
            app.ActiveWindow.SelectedSheets.Delete();
            app.WindowState = XlWindowState.xlMaximized;

            // 显示网格线
            app.ActiveWindow.DisplayGridlines = true;

            var ws = app.ActiveWorkbook.ActiveSheet as Worksheet;
            ws.Name = "统计表";

            //写单元格的数据
            app.ErrorCheckingOptions.BackgroundChecking = false;
            int lineCount = data[0].Count + 1;
            int rowCount = data.Count + 5;

            // 设置网格线
            Range rs = null;
            if (data == null)
                rs = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, lineCount]);
            else {
                rs = ws.get_Range(ws.Cells[1, 1], ws.Cells[rowCount, lineCount]);
            }
            rs.Borders.LineStyle = XlLineStyle.xlContinuous;
            rs.Borders.Weight = XlBorderWeight.xlThin;
            rs.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rs.VerticalAlignment = XlVAlign.xlVAlignCenter;

            // 把单元格格式位置为文本
            rs.NumberFormatLocal = "@";

            rs = ws.get_Range(ws.Cells[6, 2], ws.Cells[rowCount, lineCount]);
            var r = rowCount - 5;
            var c = lineCount - 1;
            var v = new string[r, c];
            for (int i = 0; i < r; i++) {
                for (int j = 0; j < c; j++) {
                    v[i, j] = d[i * c + j];
                }
            }
            rs.Value2 = v;

            var range = ws.get_Range(ws.Cells[1, 2], ws.Cells[1, lineCount]);
            range.ColumnWidth = 3.5;

            // "媒体数据" 行
            for (int i = 0; i < md.Count; i++) {
                range = ws.get_Range(ws.Cells[1, 2 + 2 * i], ws.Cells[4, 3 + 2 * i]);
                range.Merge(false);
                range.WrapText = true;
                range.Value2 = md[i];
            }

            // "总出" 行
            var zc = new List<string>();
            for (int i = 0; i < (lineCount - 1) / 2; i++) {
                zc.Add("总");
                zc.Add("出");
            }
            range = ws.get_Range(ws.Cells[5, 2], ws.Cells[5, lineCount]);
            range.Value2 = zc.ToArray();

            // 序号
            range = ws.get_Range(ws.Cells[6, 1], ws.Cells[rowCount, 1]);
            var xh = new string[xhList.Count, 1];
            for (int i = 0; i < xhList.Count; i++) {
                xh[i, 0] = xhList[i];
            }
            range.Value2 = xh;

            range = ws.get_Range(ws.Cells[1, 1], ws.Cells[5, 1]);
            range.Merge(false);
            range.Value2 = "预测次数号";
            range.WrapText = true;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.ColumnWidth = 4.5;

            //保存文件
            app.DisplayAlerts = false;
                app.ActiveWorkbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
                    XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            app.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            ShellExecute(IntPtr.Zero, new StringBuilder("Open"), new StringBuilder(fileName), new StringBuilder(""), new StringBuilder(""), 1);
        }
    }
}
